package zy.dao.shop.service.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.service.ServiceDAO;
import zy.entity.shop.service.T_Shop_Service;
import zy.entity.shop.service.T_Shop_Service_Item;
import zy.util.CommonUtil;

@Repository
public class ServiceDAOImpl extends BaseDaoImpl implements ServiceDAO{

	@Override
	public Integer count(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object ss_state = param.get("ss_state");
		Object ss_name = param.get("ss_name");
		Object ss_tel = param.get("ss_tel");
		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_shop_service t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.ss_shop_code AND sp.companyid=t.companyid ");
		sql.append(" where 1 = 1");
		if (null != ss_name && !"".equals(ss_name)) {
			sql.append(" and instr(t.ss_name,:ss_name)>0 ");
		}
		if (null != ss_tel && !"".equals(ss_tel)) {
			sql.append(" and instr(t.ss_tel,:ss_tel)>0 ");
		}
		if (null != ss_state && !"".equals(ss_state)) {
			sql.append(" and  t.ss_state = :ss_state");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(t.ss_sysdate,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(t.ss_sysdate,'%Y-%m-%d') <=:enddate");
		}
		if (shop_code != null && !shop_code.equals("")) {
			sql.append(" and (t.ss_shop_code = :shop_code or sp.sp_upcode = :shop_code)");
		}
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Shop_Service> list(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object ss_state = param.get("ss_state");
		Object ss_name = param.get("ss_name");
		Object ss_tel = param.get("ss_tel");
		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ss_id,ss_pd_no,ss_name,ss_tel,ss_startdate,ss_servicedate,ss_enddate,ss_manager,ss_pickupname,ss_question,ss_hadsolve,");
		sql.append(" ss_state,ss_sms_count,ss_satis,ss_result,ss_shop_code,ss_ca_emcode,ss_img_path,ss_ssi_id,ss_sysdate,ssi.ssi_name,sp.sp_name as ss_shop_name ");
		sql.append(" from t_shop_service t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.ss_shop_code AND sp.companyid=t.companyid ");
		sql.append(" LEFT JOIN t_shop_service_item ssi ON ssi.ssi_id=t.ss_ssi_id AND ssi.companyid=t.companyid ");
		sql.append(" where 1 = 1");
		if (null != ss_name && !"".equals(ss_name)) {
			sql.append(" and instr(t.ss_name,:ss_name)>0 ");
		}
		if (null != ss_tel && !"".equals(ss_tel)) {
			sql.append(" and instr(t.ss_tel,:ss_tel)>0 ");
		}
		if (null != ss_state && !"".equals(ss_state)) {
			sql.append(" and  t.ss_state = :ss_state");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(t.ss_sysdate,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(t.ss_sysdate,'%Y-%m-%d') <=:enddate");
		}
		if (shop_code != null && !shop_code.equals("")) {
			sql.append(" and (t.ss_shop_code = :shop_code or sp.sp_upcode = :shop_code)");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by ss_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Shop_Service> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Service.class));
		return list;
	}

	@Override
	public void save(T_Shop_Service service) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_shop_service");
		sql.append(" (ss_pd_no,ss_name,ss_tel,ss_startdate,ss_manager,ss_question,ss_state,ss_sms_count,ss_shop_code,ss_ca_emcode,");
		sql.append(" ss_ssi_id,ss_sysdate,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ss_pd_no,:ss_name,:ss_tel,:ss_startdate,:ss_manager,:ss_question,:ss_state,:ss_sms_count,:ss_shop_code,:ss_ca_emcode,");
		sql.append(" :ss_ssi_id,:ss_sysdate,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(service),holder);
	}

	@Override
	public void update(T_Shop_Service service) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_service ");
		if(service.getSs_state() == 3){
			sql.append("set ss_manager=:ss_manager,ss_state=0,ss_startdate=:ss_startdate ");
		}
		if(service.getSs_state() == 0){
			sql.append("set ss_result=:ss_result,ss_state=1,ss_servicedate=:ss_servicedate ");
		}
		if(service.getSs_state() == 1){
			sql.append("set ss_satis=:ss_satis,ss_state=2,ss_enddate=:ss_enddate,ss_pickupname=:ss_pickupname ");
		}
		sql.append(" where ss_id=:ss_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(service));
	}

	@Override
	public void del(Integer ss_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_service");
		sql.append(" WHERE ss_id=:ss_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ss_id", ss_id));
	}

	@Override
	public T_Shop_Service load(Integer ss_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ss_id,ss_pd_no,ss_name,ss_tel,ss_startdate,ss_servicedate,ss_enddate,ss_manager,ss_pickupname,ss_question,ss_hadsolve,");
		sql.append(" ss_state,ss_sms_count,ss_satis,ss_result,ss_shop_code,ss_ca_emcode,ss_img_path,ss_ssi_id,ss_sysdate,ssi.ssi_name,sp.sp_name as ss_shop_name ");
		sql.append(" from t_shop_service t ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=t.ss_shop_code AND sp.companyid=t.companyid ");
		sql.append(" LEFT JOIN t_shop_service_item ssi ON ssi.ssi_id=t.ss_ssi_id AND ssi.companyid=t.companyid ");
		sql.append(" where 1 = 1");
		sql.append(" and ss_id = :ss_id");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ss_id", ss_id),
				new BeanPropertyRowMapper<>(T_Shop_Service.class));
	}

	@Override
	public Integer list_serviceitemcount(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_shop_service_item t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.ssi_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Shop_Service_Item> list_serviceitem(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ssi_id,ssi_name,companyid");
		sql.append(" from t_shop_service_item t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.ssi_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by ssi_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Shop_Service_Item> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Service_Item.class));
		return list;
	}

	@Override
	public Integer queryItemByName(T_Shop_Service_Item service_Item) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ssi_id from t_shop_service_item");
		sql.append(" where 1=1");
		if(null != service_Item.getSsi_name() && !"".equals(service_Item.getSsi_name())){
			sql.append(" and ssi_name=:ssi_name");
		}
		if(null != service_Item.getSsi_id() && service_Item.getSsi_id() > 0){
			sql.append(" and ssi_id <> :ssi_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(service_Item) ,Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void save_serviceitem(T_Shop_Service_Item service_Item) {
		StringBuffer sql = new StringBuffer("");
		sql.append("INSERT INTO t_shop_service_item");
		sql.append(" (ssi_name,companyid)");
		sql.append(" VALUES(:ssi_name,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(service_Item),holder);
		int id = holder.getKey().intValue();
		service_Item.setSsi_id(id);
	}

	@Override
	public T_Shop_Service_Item queryServiceItemByID(Integer ssi_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select ssi_id,ssi_name from t_shop_service_item");
		sql.append(" where ssi_id=:ssi_id");
		sql.append(" limit 1");
		try{
			T_Shop_Service_Item data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("ssi_id", ssi_id),new BeanPropertyRowMapper<>(T_Shop_Service_Item.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void update_serviceitem(T_Shop_Service_Item service_Item) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_service_item");
		sql.append(" set ssi_name=:ssi_name");
		sql.append(" where ssi_id=:ssi_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(service_Item));
	}

	@Override
	public void del_serviceitem(Integer ssi_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_service_item");
		sql.append(" WHERE ssi_id=:ssi_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("ssi_id", ssi_id));
	}

}
